Create and manage database connections

A database connection object contains the necessary information for connecting to a remote database.

Create a database connection

To create a new connection:

1. From within Splunk DB Connect, select the Configuration > Databases > Connections tab.
2. select New Connection.

   Note: If you have not yet created an identity, the New Identity window appears. Create a new identity, and then you can create a new connection.

3. On the New Connection page, complete the following fields:
• Connection name
• Identity: Select the identity you want to use with this connection.
• Connection Type: A list of supported databases. Select the type of database to which you're connecting.

  Note: This list contains all supported databases, regardless of whether you installed their drivers. You must first install the database driver for the database type you want to use. If not already installed, you can install JDBC Add-ons directly from the DB Connect application during connection setup. In that case, a pop-up window will appear and you will need to follow the installation instructions. After the driver is installed, it takes approximately 10 seconds for it to be available on the system. You must first install the corresponding driver in order to select a database from this list. For information about the Microsoft SQL Server options listed here, see "Microsoft SQL Server".

• Timezone: If you want to convert data of date and time types read from the database into the Splunk server's local time zone, enter the source database time zone for the conversion. When you set the time zone, you have two time zone conversion options depending on the localTimezoneConversionEnabled setting in db_connections.conf:
  • When you don't define localTimezoneConversionEnabled or set it to false, the system only converts index time values read from table columns in the source database into the local time zone. This conversion option only applies to inputs.
  • When you set localTimezoneConversionEnabled to true, the system converts all date and time data type (datetime, time, date, timestamp) values into the local time zone except source datetime values that already include time zone information For example, TIMESTAMP WITH TIMEZONE). This conversion option applies to inputs, dbxqueries, and dblookups.
  If you leave the Timezone field blank, the system assumes that the source database time zone and Splunk server's local time zone are the same and reads the date and time data as is.
  Example

  	Timestamp in database	Database time zone	Splunk Server (JVM) time zone	Indexed timestamp value
  Without timezone conversion	2006-02-15 04:34:33	UTC	Asia/Shanghai (GMT+8)	2006-02-15 04:34:33 in Asia/Shanghai time zone
  With timezone conversion	2006-02-15 04:34:33	UTC	Asia/Shanghai (GMT+8)	2006-02-15 12:34:33 in Asia/Shanghai time zone

  Note:

  • If the column in your database contains timezone information For example, for the column with a TIMESTAMP WITH TIMEZONE type), Splunk ignores the timezone you set here.
  • The timezone setting defaults to JVM time zone. If there are no JVM time zone settings, Splunk uses the time zone of your operating system.
  • For MySQL database driver, you need to set useLegacyDatetimeCode setting to false in the JDBC URL if you want to use the timezone setting.

JDBC URL Settings

• Host: Enter the address, or host, of the database.
• Port: (Optional.) Enter the port number of the database. You don't need to enter a port number here if your database is using its default port.
• Default Database: Enter the default database or catalog name for the database type you chose. The usage and meaning of this parameter varies between database vendors, so check your database vendor's documentation. For more information, see supported databases matrix.
• Enable SSL: Select this check box to enable Secure Sockets Layer (SSL) encryption for the connection. SSL support is not available for all connection types. For further information, see supported databases matrix and Enable SSL for your database connection. To find out how to connect to Oracle using SSL for encryption, see Connect to Oracle using SSL (for encryption only).
• Certificate: When you select the Enable SSL check box, the Certificate field appears for the MySQL, MSSQL and Oracle connection types. You can pass a certificate which Splunk automatically adds to the Java TrustStore and uses it for the server authentication. If you select the Enable SSL check box but leave the certificate field empty, then DB Connect ignores the certificate and functions as before.
• JDBC URL Preview A Java Database Connectivity (JDBC) Uniform Resource Locator (URL) is a URL that encodes all the necessary information for connecting to your database. The JDBC URL Preview field is not editable by default, but Splunk populates this field, using placeholder values, with the correct URL format according to the database type you have chosen.
• Connection properties Splunk uses the provided properties during the creation of the connection. Splunk provides properties either here or through the editing of the JDBC URL. You can find a list of all valid driver connection properties in the official database documentation. Splunk ignores incorrect properties.
• You can manually edit the URL format by selecting Edit JDBC URL. When you select this field, the JDBC URL Preview becomes editable. This is useful if you need to add customized JDBC URL parameters to the JDBC URL, but that is not Splunk best practice unless you already know what customizations you want to add. Connection parameters vary among JDBC drivers. Consult your database vendor's documentation for a list of supported parameters and values.

Note: DB Connect supports LDAP connection, you need to select the LDAP URL in JDBC URL field. Consult your database vendor's documentation on how to generate LDAP URL.

Advanced Settings

• Fetch Size: (Optional.) Enter the number of rows to return at a time from the database. If you leave this field blank, it defaults to 300.
• Readonly: Select this check box to indicate your intention for users to only use SELECT statements with the database. Be aware that this cannot always guarantee read-only access. DB Connect tries to ensure that it's read-only, but it is the database driver that ultimately allows or prevents changes. If you intend to use the read-only option, ensure that, on the database itself, the user you're connecting as only has read-only access. See Read-only connections for more details.
4. In the Permissions table, update the Splunk Enterprise permissions for this database connection. For more information, see Permissions.
5. Select Save to save the connection.

   Note:If the connection you create is valid, you can save the connection successfully. Otherwise Splunk displays an error message for you to check the configuration of the connection and save again.

Connection Properties

To enable a flexible way to use all the capabilities of the JDBC driver, we can add connection properties. You can add plain text properties and encrypted properties.

Plain Properties

In the connection form, go to Add Property > Plain Property. These properties are stored in plain text and can be used in most cases.

Encrypted Properties

In the connection form, go to Add Property > Encrypted Property. These properties are stored in encrypted form, making them suitable for passwords and private keys.

Note: In case you have selected an identity for a connection, but also define the user and password as a connection property, then the connection property will take precedence.

Use Cases

When creating a connection to Google BigQuery using a Google Service Account authentication mechanism, you can securely add an encrypted property for the OAuthPvtKey property.

Edit connections

Go to Configuration > Databases > Connections to see a list of the defined database connections.
Note: The list of connections that you can see depends on the permissions set on each connection. For more information, see Permissions.

Connection actions

You can make changes to a connection using the following buttons on the connection page under the Edit tab:

• Disable/Enable: Disable/Enable a connection by selecting Disable/Enable here. You cannot disable a connection if any inputs, outputs, or lookups are using it. In that case, Splunk greys out this button.
• Edit: Edit the connection by clicking its name or edit button.
• Clone: Creates a copy of the connection. You must give the copy a new name.
• Delete: Deletes the connection. You cannot delete a connection if any inputs, outputs, or lookups are using it. In that case, Splunk greys out this button.

You can also edit the attributes of a connection listed in Create a database connection, except its name. To change the name of a connection, clone it, give the clone the name you want, and then delete the original connection.

Permissions

The Permissions table is where you select the Splunk Enterprise roles that have read, read-write, or no access to the connection.

• Read access means that Splunk Enterprise roles can use the connection.
• Write access means that Splunk Enterprise roles can use and modify the connection.
  

By default, the Splunk Enterprise "admin" and "db_connect_admin" roles have write access to a new connection, the "db_connect_user" role has read access, and all other roles have no access.

Add support for a new database vendor

You may need to ingest data into Splunk from a database not supported in DB Connect. If you've found a JDBC driver for that specific database vendor, you'll probably be able to handle it.

1. Download the JDBC driver.
2. Copy the driver under $SPLUNK_HOME/etc/apps/splunk_app_db_connect/drivers directory.
3. Edit or create $SPLUNK_HOME/etc/apps/splunk_app_db_connect/local/db_connection_types.conf file.

   [<Stanza for the new database vendor>] 
   
   displayName = <Name to identify the database vendor from the UI>
   
   serviceClass = None
   
   jdbcUrlFormat = <Review the JDBC driver documentation and look for information about the connection URL format. <host>, <port> and <database> are tags you can use to help build it dynamically>
   
   jdbcDriverClass = <Review the JDBC driver documentation and look for information about the driver class>
   
   port = <Default port>
   
   testQuery = <A generic SQL query to make it possible to test the connection configuration>
   

   Note: For more information on all configuration options, see Connection Type Specifications file.

4. Restart Splunk.

Connection type deprecation

Since version 3.16.0 we have introduced a feature to deprecate connection types. It is useful when we need to switch from legacy JDBC drivers to new solutions and ensure a seamless configuration.

Please note that if you are using a connection type that is deprecated, you will still be able to use it, in which case a warning message will be displayed like: Connection type <Name> is deprecated since version <Version>.

You will not be able to create new connections for deprecated connection types.